﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using DAL.Entities;
using System.Data.SqlClient;
using System.Data;

namespace DAL
{
    public class UsuarioDAL : DBController
    {
        public UsuarioDAL() { 
        
        }

        public int LoginUser(UsuarioEntity user){
            try
            {
                SqlConnection conn = this.OpenConn();

                SqlDataReader dr;

                SqlCommand dCmd = new SqlCommand("MARAVILLA_SQL.spLoginUser", conn);

                dCmd.CommandText = "MARAVILLA_SQL.spLoginUser";

                dCmd.CommandType = CommandType.StoredProcedure;


                dCmd.Parameters.AddWithValue("@username", user.VarUsername);
                dCmd.Parameters.AddWithValue("@password", user.VarPassword);
                
                // dCmd.ExecuteNonQuery();
                dr = dCmd.ExecuteReader();
                dCmd.Dispose();
                dr.Read();
                //Guardamos el resultado pra devolverlo
                int res =  (int)dr.GetValue(0);
                return res;

            }catch(Exception ex){
                throw ex;
            }
        }

        public bool SetPasswordByUsername(UsuarioEntity user)
        {
            try
            {
                SqlConnection conn = this.OpenConn();

                SqlDataReader dr;

                SqlCommand dCmd = new SqlCommand("MARAVILLA_SQL.spSetPasswordByUsername", conn);

                dCmd.CommandText = "MARAVILLA_SQL.spSetPasswordByUsername";

                dCmd.CommandType = CommandType.StoredProcedure;


                dCmd.Parameters.AddWithValue("@username", user.VarUsername);
                dCmd.Parameters.AddWithValue("@password", this.GetSHA256(user.VarPassword));

                // dCmd.ExecuteNonQuery();
                dr = dCmd.ExecuteReader();
                dCmd.Dispose();
                dr.Read();
                return true;

            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// Funcion que busca un usaurio por su password y username
        /// </summary>
        /// <param name="usr"></param>
        /// <returns></returns>
        public UsuarioEntity GetUsuarioByPasswordAndUsername(UsuarioEntity usr)
        {
            try
            {
                UsuarioEntity user = new UsuarioEntity();
                using (SqlConnection conn = this.OpenConn())
                {

                    SqlDataReader dr;

                    SqlCommand dCmd = new SqlCommand("MARAVILLA_SQL.spGetUsuarioByPasswordAndUsername", conn);

                    dCmd.CommandText = "MARAVILLA_SQL.spGetUsuarioByPasswordAndUsername";

                    dCmd.CommandType = CommandType.StoredProcedure;


                    dCmd.Parameters.AddWithValue("@varUsername", usr.VarUsername);
                    dCmd.Parameters.AddWithValue("@varPassword", usr.VarPassword);

                    // dCmd.ExecuteNonQuery();
                    dr = dCmd.ExecuteReader();
                    dCmd.Dispose();
                    dr.Read();

                    //Creamos el rol para cargarlo
                    RolEntity rol = new RolEntity();
                    rol.IntIdRol = (int)dr.GetValue(4);
                    rol.VarNombre = (string)dr.GetValue(5);
                    rol.BitIsDeleted = (bool)dr.GetValue(6);

                    //Guardamos el resultado pra devolverlo
                    user.IntIdUsuario = (int)dr.GetValue(0);
                    user.VarUsername = (string)dr.GetValue(1);
                    user.BitIsDeleted = (bool)dr.GetValue(2);
                    user.IntCantIntentos = (int)dr.GetValue(3);
                    user.Rol = rol;
                }
                return user;
            }
            catch
            {
                throw new Exception("El usuario no exsiste o se encuentra inhabilitado");
            }
        }


        /// <summary>
        /// Funcion que busca un usaurio por su id
        /// </summary>
        /// <param name="usr"></param>
        /// <returns></returns>
        public UsuarioEntity GetUsuarioById(UsuarioEntity usr)
        {
            try
            {
                UsuarioEntity user = new UsuarioEntity();
                using (SqlConnection conn = this.OpenConn())
                {

                    SqlDataReader dr;
                    SqlCommand dCmd = new SqlCommand("MARAVILLA_SQL.spGetUsuarioById", conn);

                    dCmd.CommandText = "MARAVILLA_SQL.spGetUsuarioById";

                    dCmd.CommandType = CommandType.StoredProcedure;


                    dCmd.Parameters.AddWithValue("@intIdUsuario", usr.IntIdUsuario);

                    // dCmd.ExecuteNonQuery();
                    dr = dCmd.ExecuteReader();
                    dCmd.Dispose();
                    dr.Read();

                    //Guardamos el resultado pra devolverlo
                    user.IntIdUsuario = (int)dr.GetValue(2);
                    user.VarUsername = (string)dr.GetValue(0);
                    user.VarPassword = (string)dr.GetValue(1);
                    user.BitIsDeleted = (bool)dr.GetValue(5);
                    user.IntCantIntentos = (int)dr.GetValue(3);
                }
                return user;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public List<FuncionalidadEntity> GetFuncionalidesById(RolEntity rol)
        {
            try
            {
                List<FuncionalidadEntity> funcionalidades = new List<FuncionalidadEntity>();
                using (SqlConnection conn = this.OpenConn())
                {

                    SqlDataReader dr;

                    SqlCommand dCmd = new SqlCommand("MARAVILLA_SQL.spGetFuncionalidesById", conn);

                    dCmd.CommandText = "MARAVILLA_SQL.spGetFuncionalidesById";

                    dCmd.CommandType = CommandType.StoredProcedure;


                    dCmd.Parameters.AddWithValue("@intIdRol", rol.IntIdRol);

                    // dCmd.ExecuteNonQuery();
                    dr = dCmd.ExecuteReader();
                    dCmd.Dispose();
                    if(dr.HasRows){
                        while(dr.Read()){
                            FuncionalidadEntity fn = new FuncionalidadEntity();
                            fn.IntIdFuncionalidad = (int)dr.GetValue(0);
                            fn.VarNombre = (string)dr.GetValue(1);
                            fn.VarEtiqueta = (string)dr.GetValue(2);
                            fn.BitIsDeleted = (bool)dr.GetValue(3);
                            funcionalidades.Add(fn);
                        }
                    }
                }
                    //Guardamos el resultado pra devolverlo
                return funcionalidades;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
    }
}
